--**中心分拣量统计
with dm_center_count_dt1 as (
select scan_time                                       --扫描日期
     , network_code                                    --网点编码
     , null as                          equipment_code --设备编码
     , null as                          operate_type
     , null as                          sorting_plan_code
     , null as                          supply_desk_code
     , null as                          grid_code
     , null as                          grid_no
     , '1'                              cnt_type
     , agency_area_name
     , agency_area_code
     , franchisee_name
     , franchisee_code
     , network_name
     , cast(owned_venue_type as string) owned_venue_type
     , center_sort_cnt                  sort_cnt
     , center_zcsort_cnt                zcsort_cnt
     , center_ycsort_cnt                ycsort_cnt
     , null as                          equipment_cfsort_cnt
     , null as                          sort_topcnt_min
     , null as                          sort_toptime_min
     , null as                          grid_name
     , replace(dt ,' ','-')             as dt
from (
      select date_format(scan_time, 'yyyy-MM-dd HH')           scan_time
           , franchisee_code                                                  --加盟商编码
           , franchisee_name                                                  --加盟商名称
           , agency_area_code                                                 --代理商编码
           , agency_area_name                                                 --代理商名称
           , network_code                                                     --网点编码
           , network_name                                                     --网点名称
           , owned_venue_type
           , count(waybill_no)                              center_sort_cnt   --中心分拣总量
           , count(if(grid_code = '111', waybill_no, null)) center_zcsort_cnt --中心正常分拣量
           , count(if(grid_code = '111', null, waybill_no)) center_ycsort_cnt --中心异常分拣量
           , date_format(scan_time, 'yyyy-MM-dd HH')           dt                --时间分区
      from jms_dm.dm_center_sort_ass_detail_hi
      where dt between '{{ execution_date | date_add(-7) | cst_ds }}-00' and '{{ execution_date | cst_ds }}-23'
        and date(scan_time) between  '{{ execution_date | date_add(-4) | cst_ds }}' and  '{{ execution_date | cst_ds }}'
      group by franchisee_code, franchisee_name, agency_area_code, agency_area_name,
               network_code, network_name, owned_venue_type, date_format(scan_time, 'yyyy-MM-dd HH')
     ) t
),
--供件台供件量统计
dm_supply_desk_count_dt as (
--字段顺序纠正
select
     scan_time      --扫描日期
     , network_code   --网点编码
     , equipment_code --设备编码
     , operate_type
     , sorting_plan_code
     , supply_desk_code
     , null                as           grid_code
     , null                as           grid_no
     , '3'                              cnt_type
     , agency_area_name
     , agency_area_code
     , franchisee_name
     , franchisee_code
     , network_name
     , cast(owned_venue_type as string) owned_venue_type
     , supply_desk_cnt                  sort_cnt
     , null                as           zcsort_cnt
     , null                as           ycsort_cnt
     , null                as           equipment_cfsort_cnt
     , supply_desk_topcnt  as           sort_topcnt_min
     , supply_desk_toptime as           sort_toptime_min
     , null                as           grid_name
     , replace(dt ,' ','-')             as dt
from (
      select network_code                                             --网点编码
           , network_name                                             --网点名称
           , date_format(scan_time, 'yyyy-MM-dd HH') scan_time           --扫描时间日期
           , agency_area_name                                         --代理商名称
           , agency_area_code                                         --代理商编码
           , franchisee_name                                          --加盟商名称
           , franchisee_code                                          --加盟商编码
           , cast(owned_venue_type as string)     owned_venue_type
           , equipment_code                                           --设备编码
           , operate_type                                             --操作模式
           , sorting_plan_code--分拣方案
           , supply_desk_code                                         --供包台，编码
           , supply_desk_cnt                                          --供包台总量
           , supply_desk_cnt_min                  supply_desk_topcnt  --供件台峰值操作量
           , scan_time                            supply_desk_toptime --扫描峰值时间
           , date_format(scan_time, 'yyyy-MM-dd HH') dt
      from (--排序
            SELECT network_code                                                                                                                                                                                                                               --网点编码
                 , network_name                                                                                                                                                                                                                               --网点名称
                 , scan_time                                                                                                                                                                                                                                  --扫描时间到分钟
                 , agency_area_name                                                                                                                                                                                                                           --代理商名称
                 , agency_area_code                                                                                                                                                                                                                           --代理商编码
                 , franchisee_name                                                                                                                                                                                                                            --加盟商名称
                 , franchisee_code                                                                                                                                                                                                                            --加盟商编码
                 , owned_venue_type                                                                                                                                                                                                                           --场地属性
                 , equipment_code
                 , operate_type                                                                                                                                                                                                                               --操作模式
                 , sorting_plan_code--分拣方案
                 , supply_desk_code                                                                                                                                                                                                                           --供包台，编码
                 , supply_desk_cnt   as supply_desk_cnt_min --每分供件台供件数
                 , sum(supply_desk_cnt)
                       over (partition by network_code,supply_desk_code,agency_area_code,franchisee_code,owned_venue_type,equipment_code,operate_type,sorting_plan_code,DATE_FORMAT(scan_time, 'yyyy-MM-dd HH'))
                       as   supply_desk_cnt     --供包台总量
                 , ROW_NUMBER()
                    over (partition by network_code,supply_desk_code,agency_area_code,franchisee_code,owned_venue_type,equipment_code,operate_type,sorting_plan_code,DATE_FORMAT(scan_time, 'yyyy-MM-dd HH') order by supply_desk_cnt desc )
                       as  cnt_rk
            from (--每分钟统计供包台供件量
                     SELECT network_code                                         --网点编码
                          , network_name                                         --网点名称
                          , date_format(scan_time, 'yyyy-MM-dd HH:mm') scan_time --扫描时间到分钟
                          , agency_area_name                                     --代理商名称
                          , agency_area_code                                     --代理商编码
                          , franchisee_name                                      --加盟商名称
                          , franchisee_code                                      --加盟商编码
                          , owned_venue_type                                     --场地属性
                          , equipment_code                                       --设备编码
                          , operate_type                                         --操作模式
                          , sorting_plan_code--分拣方案
                          , supply_desk_code                                     --供包台，编码
                          , count(waybill_no)                          supply_desk_cnt
                    from jms_dm.dm_center_sort_ass_detail_hi
                    where dt between '{{ execution_date | date_add(-7) | cst_ds }}-00' and '{{ execution_date | cst_ds }}-23'
                      and date(scan_time) between  '{{ execution_date | date_add(-4) | cst_ds }}' and  '{{ execution_date | cst_ds }}'
                     group by network_code, network_name, date_format(scan_time, 'yyyy-MM-dd HH:mm')
                            , agency_area_name, agency_area_code, franchisee_name, franchisee_code
                            , owned_venue_type
                            , supply_desk_code, equipment_code, operate_type, sorting_plan_code
             ) scan_detail2
           ) scan_detail3
      where cnt_rk = 1
     ) t
),
--**异常统计
dm_sort_abnormal_count_dt as (
    select scan_time      --扫描日期
         , network_code   --网点编码
         , equipment_code --设备编码
         , operate_type
         , sorting_plan_code
         , null as      supply_desk_code
         , grid_code
         , null as      grid_no
         , '4'          cnt_type
         , agency_area_name
         , agency_area_code
         , franchisee_name
         , franchisee_code
         , network_name
         , owned_venue_type
         , abnormal_cnt sort_cnt
         , null as      zcsort_cnt
         , null as      ycsort_cnt
         , null as      equipment_cfsort_cnt
         , null as      sort_topcnt_min
         , null as      sort_toptime_min
         , grid_name
         , replace(dt ,' ','-')  as dt
         from (
              SELECT network_code                                   --网点编码
                   , network_name                                   --网点名称
                   , date_format(scan_time, 'yyyy-MM-dd HH') scan_time --扫描日期
                   , agency_area_name                               --代理商名称
                   , agency_area_code                               --代理商编码
                   , franchisee_name                                --加盟商名称
                   , franchisee_code                                --加盟商编码
                   , cast(owned_venue_type as string)     owned_venue_type
                   , equipment_code                                 --设备编码
                   , operate_type                                   --操作模式
                   , sorting_plan_code--分拣方案
                   , grid_code
                   , case
                         when grid_code = '995' then '三段码请求超时 (三段码查询失败)'
                         when grid_code = '996' then '三段码未配置(分拣方案未维护)'
                         when grid_code = '997' then '超最大循环圈数'
                         when grid_code = '999' then '拦截件'
                         when grid_code = '998' then '取消件'
                         when grid_code = '993' then '未识别条码'
                         when grid_code = '994' then '多条码'
                         when grid_code = '111' then '正常读码'
                   end as                                  grid_name
                   , count(waybill_no)                    abnormal_cnt
                   , date_format(scan_time, 'yyyy-MM-dd HH') dt
              from jms_dm.dm_center_sort_ass_detail_hi
              where dt between '{{ execution_date | date_add(-7) | cst_ds }}-00' and '{{ execution_date | cst_ds }}-23'
              and grid_code != '111'
              and date(scan_time) between  '{{ execution_date | date_add(-4) | cst_ds }}' and  '{{ execution_date | cst_ds }}'
              group by network_code, network_name, date_format(scan_time, 'yyyy-MM-dd HH'), agency_area_name
                     , agency_area_code, franchisee_name, franchisee_code, owned_venue_type
                     , equipment_code, operate_type, sorting_plan_code, grid_code
              ) t
),
--**格口落格统计量
dm_grid_falling_count_dt as (
    select scan_time      --扫描日期
         , network_code   --网点编码
         , equipment_code --设备编码
         , operate_type
         , sorting_plan_code
         , null as  supply_desk_code
         , grid_code
         , grid_no
         , '5'      cnt_type
         , agency_area_name
         , agency_area_code
         , franchisee_name
         , franchisee_code
         , network_name
         , owned_venue_type
         , grid_cnt sort_cnt
         , null as  zcsort_cnt
         , null as  ycsort_cnt
         , null as  equipment_cfsort_cnt
         , null as  sort_topcnt_min
         , null as  sort_toptime_min
         , case when grid_code = '995' then '三段码请求超时 (三段码查询失败)'
                when grid_code = '996' then '三段码未配置(分拣方案未维护)'
                when grid_code = '997' then '超最大循环圈数'
                when grid_code = '999' then '拦截件'
                when grid_code = '998' then '取消件'
                when grid_code = '993' then '未识别条码'
                when grid_code = '994' then '多条码'
                when grid_code = '111' then '正常读码'
                end as  grid_name
         , replace(dt ,' ','-')  as dt
    from (
             SELECT network_code                                   --网点编码
                  , network_name                                   --网点名称
                  , date_format(scan_time, 'yyyy-MM-dd HH') scan_time --扫描日期
                  , agency_area_name                               --代理商名称
                  , agency_area_code                               --代理商编码
                  , franchisee_name                                --加盟商名称
                  , franchisee_code                                --加盟商编码
                  , cast(owned_venue_type as string)     owned_venue_type
                  , equipment_code                                 --设备编码
                  , operate_type                                   --操作模式
                  , sorting_plan_code--分拣方案
                  , grid_no                                        --供包台，编码
                  , count(waybill_no)                    grid_cnt
                  , date_format(scan_time, 'yyyy-MM-dd HH') dt
                  , grid_code
             from jms_dm.dm_center_sort_ass_detail_hi scan_detail
             WHERE dt between  '{{ execution_date | date_add(-7) | cst_ds }}-00' and  '{{ execution_date | cst_ds }}-23'
             and date(scan_time) between  '{{ execution_date | date_add(-4) | cst_ds }}' and  '{{ execution_date | cst_ds }}'
             group by network_code, network_name, date_format(scan_time, 'yyyy-MM-dd HH'), agency_area_name
                    , agency_area_code, franchisee_name, franchisee_code, owned_venue_type
                    , grid_no, equipment_code, operate_type, sorting_plan_code,grid_code
         ) t
),
--**设备分拣统计
dm_equipment_sort_count_dt as
    (
        select scan_time      --扫描日期
             , network_code   --网点编码
             , equipment_code --设备编码
             , operate_type
             , sorting_plan_code
             , null as                supply_desk_code
             , null as                grid_code
             , null as                grid_no
             , '2'                    cnt_type
             , agency_area_name
             , agency_area_code
             , franchisee_name
             , franchisee_code
             , network_name
             , owned_venue_type
             , equipment_sort_cnt     sort_cnt
             , equipment_zcsort_cnt   zcsort_cnt
             , equipment_ycsort_cnt   ycsort_cnt
             , equipment_cfsort_cnt
             , equipment_sort_topcnt  sort_topcnt_min
             , equipment_sort_toptime sort_toptime_min
             , null as                grid_name
             , replace(dt ,' ','-')  as dt
        from ( --筛检出 分拣总量最高的那一条数据
                 select network_code                                                --网点编码
                      , network_name                                                --网点名称
                      , scan_time                                                   --扫描日期
                      , agency_area_name                                            --代理商名称
                      , agency_area_code                                            --代理商编码
                      , franchisee_name                                             --加盟商名称
                      , franchisee_code                                             --加盟商编码
                      , cast(owned_venue_type as string)     owned_venue_type
                      , equipment_code                                              --设备编码
                      , operate_type                                                --操作模式
                      , sorting_plan_code--分拣方案
                      , equipment_sort_cnt                                          --中心分拣总量
                      , equipment_zcsort_cnt                                        --中心正常分拣量
                      , equipment_ycsort_cnt                                        --中心异常分拣量
                      , equipment_cfsort_cnt                                        --设备错分量
                      , min_sortcnt                          equipment_sort_topcnt  --设备扫描峰值
                      , scan_time_min                        equipment_sort_toptime --设备扫描峰值时间 'yyyy-MM-dd  HH:mm'
                      , date_format(scan_time, 'yyyy-MM-dd HH') dt
                 from ( --筛检出 分拣总量最高的那一条数据 排序
                          select network_code                                                                                                                                                                   --网点编码
                               , network_name                                                                                                                                                                   --网点名称
                               , date_format(scan_time, 'yyyy-MM-dd HH')                                                                                                                                  scan_time--扫描日期
                               , agency_area_name                                                                                                                                                               --代理商名称
                               , agency_area_code                                                                                                                                                               --代理商编码
                               , franchisee_name                                                                                                                                                                --加盟商名称
                               , franchisee_code                                                                                                                                                                --加盟商编码
                               , grid_code                                                                                                                                                                      --格口编码/异常编码
                               , owned_venue_type                                                                                                                                                               --场地属性
                               , equipment_code                                                                                                                                                                 --设备编码
                               , operate_type                                                                                                                                                                   --操作模式
                               , sorting_plan_code--分拣方案
                               , equipment_sort_cnt                                                                                                                                                             --设备分拣总量
                               , equipment_cfsort_cnt                                                                                                                                                           --设备错分总量
                               , equipment_zcsort_cnt
                               , equipment_ycsort_cnt
                               , min_sortcnt
                               , scan_time_min
                               , ROW_NUMBER()
                                  over (partition by network_code,equipment_code,scan_time,agency_area_code,franchisee_code,owned_venue_type,operate_type,sorting_plan_code order by min_sortcnt desc) rk
                          from ( --取一天的分拣总量和错分量
                                  select network_code                                                                                                                                                                                   --网点编码
                                        , network_name                                                                                                                                                                                   --网点名称
                                        , date_format(scan_time, 'yyyy-MM-dd HH')                                                                                                                                  scan_time--扫描日期
                                        , agency_area_name                                                                                                                                                                               --代理商名称
                                        , agency_area_code                                                                                                                                                                               --代理商编码
                                        , franchisee_name                                                                                                                                                                                --加盟商名称
                                        , franchisee_code                                                                                                                                                                                --加盟商编码
                                        , grid_code                                                                                                                                                                                      --格口编码/异常编码
                                        , owned_venue_type                                                                                                                                                                               --场地属性
                                        , equipment_code                                                                                                                                                                                 --设备编码
                                        , operate_type                                                                                                                                                                                   --操作模式
                                        , sorting_plan_code--分拣方案
                                        , sum(min_sortcnt_type)
                                              over (partition by network_code,equipment_code,date_format(scan_time, 'yyyy-MM-dd HH'),agency_area_code,franchisee_code,owned_venue_type,operate_type,sorting_plan_code)
                                              as equipment_sort_cnt   --设备分拣总量
                                        , sum(is_errsort_min)
                                              over (partition by network_code,equipment_code,date_format(scan_time, 'yyyy-MM-dd HH'),agency_area_code,franchisee_code,owned_venue_type,operate_type,sorting_plan_code)
                                              AS equipment_cfsort_cnt --设备错分总量
                                        , sum(if(grid_code = '111', min_sortcnt_type, 0))
                                              over (partition by network_code,equipment_code,date_format(scan_time, 'yyyy-MM-dd HH'),agency_area_code,franchisee_code,owned_venue_type,operate_type,sorting_plan_code) equipment_zcsort_cnt
                                        , sum(if(grid_code = '111', 0, min_sortcnt_type))
                                              over (partition by network_code,equipment_code,date_format(scan_time, 'yyyy-MM-dd HH'),agency_area_code,franchisee_code,owned_venue_type,operate_type,sorting_plan_code) equipment_ycsort_cnt
                                        , sum(min_sortcnt_type)
                                              over (partition by network_code,equipment_code,scan_time,agency_area_code,franchisee_code,owned_venue_type,operate_type,sorting_plan_code)                            min_sortcnt
                                        , scan_time                                                                                                                                                                 scan_time_min
                                   from ( --group by
                                            select network_code                       --网点编码
                                                 , network_name                       --网点名称
                                                 , scan_time                          --扫描日期
                                                 , agency_area_name                   --代理商名称
                                                 , agency_area_code                   --代理商编码
                                                 , franchisee_name                    --加盟商名称
                                                 , franchisee_code                    --加盟商编码
                                                 , grid_code                          --格口编码/异常编码
                                                 , owned_venue_type                   --场地属性
                                                 , equipment_code                     --设备编码
                                                 , operate_type                       --操作模式
                                                 , sorting_plan_code--分拣方案
                                                 , sum(is_errsort)   is_errsort_min   --是否错分 分正常异常的类型
                                                 , count(waybill_no) min_sortcnt_type --设备每分钟的操作量 分正常异常的类型
                                            from ( --与错分表join
                                                     select waybill_no                                            --运单号
                                                          , network_code                                          --网点编码
                                                          , network_name                                          --网点名称
                                                          , date_format(scan_time, 'yyyy-MM-dd HH:mm') scan_time  --扫描时间
                                                          , agency_area_name                                      --代理商名称
                                                          , agency_area_code                                      --代理商编码
                                                          , franchisee_name                                       --加盟商名称
                                                          , franchisee_code                                       --加盟商编码
                                                          , grid_code                                             --格口编码/异常编码
                                                          , owned_venue_type                                      --场地属性
                                                          , equipment_code                                        --设备编码
                                                          , operate_type                                          --操作模式
                                                          , sorting_plan_code--分拣方案
                                                          , if(miss_sort.billcode is not null, 1, 0)   is_errsort --是否错分
                                                     from jms_dm.dm_center_sort_ass_detail_hi scan_detail
                                                     left JOIN
                                                     (  select billcode
                                                        from jms_dm.dm_tab_missrate_waybill_detail_dt
                                                        where dt between  '{{ execution_date | date_add(-5) | cst_ds }}'and  '{{ execution_date | cst_ds }}'
                                                        group by billcode
                                                     ) miss_sort
                                                  on scan_detail.waybill_no = miss_sort.billcode
                                                  where scan_detail.dt between '{{ execution_date | date_add(-7) | cst_ds }}-00' and '{{ execution_date | cst_ds }}-23'
                                                       and date(scan_detail.scan_time) between  '{{ execution_date | date_add(-4) | cst_ds }}' and  '{{ execution_date | cst_ds }}'
                                                 ) equipment_miss_sort
                                            group by network_code, network_name, scan_time, agency_area_name,
                                                     agency_area_code, franchisee_name, franchisee_code,
                                                     grid_code, owned_venue_type, equipment_code, operate_type,
                                                     sorting_plan_code--分拣方案
                                        ) equipment_miss_sort_top
                               ) equipment_sort_cnt
                      ) equipment_miss_sort_top2
                 where rk = 1
             ) t
)

insert overwrite table jms_dm.dm_center_sort_count_dt partition (dt)
select scan_time,
       network_code,
       equipment_code,
       operate_type,
       sorting_plan_code,
       supply_desk_code,
       grid_code,
       grid_no,
       cnt_type,
       agency_area_name,
       agency_area_code,
       franchisee_name,
       franchisee_code,
       network_name,
       owned_venue_type,
       sort_cnt,
       zcsort_cnt,
       ycsort_cnt,
       equipment_cfsort_cnt,
       sort_topcnt_min,
       sort_toptime_min,
       grid_name,
       ROW_NUMBER() over (partition by scan_time order by scan_time) id,
       concat(scan_time,':00:00') AS scan_time_hms,
       substr(dt,1,10) as dt
from (
         select * from dm_center_count_dt1
         union all
         select * from dm_supply_desk_count_dt
         union all
         select * from dm_sort_abnormal_count_dt
         union all
         select * from dm_grid_falling_count_dt
         union all
         select * from dm_equipment_sort_count_dt
     ) t

 distribute by 1;